package com.elock.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.elock.base.core.Page;
import com.elock.base.core.Pageable;
import com.elock.base.core.dao.impl.DaoCenter;
import com.elock.base.core.util.ConvertUtil;
import com.elock.base.core.util.ExceptionUtil;
import com.elock.base.core.util.WebUtils;
import com.elock.entity.Car;

/**
 * Dao - 车辆
 * @author 仁德
 */
@Repository("carDao")
public class CarDao extends DaoCenter {

//	@Resource(name = "saleOrgBaseServiceImpl")
//	private SaleOrgBaseService saleOrgBaseService;

	public Page<Map<String, Object>> findPage(String carNumber, String carType,
			Long saleOrgId, Pageable pageable) {

		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();

		List<Object> list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		sql.append(
				"select c.*,so.name sale_org_name from car c left join car_sale_org cso on c.id=cso.car");
		sql.append(" left join sale_org so on so.id=cso.sale_org where 1=1");

		if (companyInfoId != null) {
			sql.append(" and c.company_info_id=?");
			list.add(companyInfoId);
		}
		if (!ConvertUtil.isEmpty(carNumber)) {
			sql.append(" and c.car_number like ?");
			list.add("%" + carNumber + "%");
		}
		if (!ConvertUtil.isEmpty(carType)) {
			sql.append(" and c.car_type like ?");
			list.add("%" + carType + "%");
		}
		if (saleOrgId != null) {
			sql.append(" and so.id=?");
			list.add(saleOrgId);
		}
//		sql.append(" and so.id in (" + saleOrgBaseService.saleOrgAuth() + ")");

		sql.append(" order by c.create_date desc");

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		Page<Map<String, Object>> getPage = getNativeDao().findPageMap(sql.toString(),
				objs,
				pageable);

		return getPage;

	}

	public List<Map<String, Object>> findElockCarByCarId(Long carId) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				"select e.id,e.elock_num elockNum,e.elock_name elockName,ec.position,ec.seq,ec.berth,sdt.value type_value,sdm.value model_value,ers.status");
		sql.append(" from elock_car ec left join elock e on ec.elock_id=e.id");
		sql.append(" left join system_dict sdt on e.type=sdt.id");
		sql.append(" left join system_dict sdm on e.model= sdm.id");
		sql.append(" left join elock_realtime_status ers on ers.elock_id= e.id");
		sql.append(" where ec.car_id=" + carId);
		sql.append(" order by ec.create_date desc");

		List<Map<String, Object>> maps = getNativeDao().findListMap(sql.toString(),
				null,
				0);

		return maps;
	}

	public Car findByCarNumber(String carNumber) {

		if (ConvertUtil.isEmpty(carNumber)) {
			ExceptionUtil.throwDaoException("value is empty!");
		}
		String sql = "select * from car where car_number = ?";
		Car car = getNativeDao().findSingleManaged(sql,
				new Object[] { carNumber },
				Car.class);
		return car;
	}

	public List<Map<String, Object>> findByTerminalId(Long terminalId) {

		StringBuilder sql = new StringBuilder();
		sql.append("select c.* from terminal_car tc,car c");
		sql.append(" where tc.car_id=c.id and tc.terminal_id=?");

		List<Map<String, Object>> maps = getNativeDao().findListMap(sql.toString(),
				new Object[] { terminalId },
				0);

		return maps;
	}

	public Page<Map<String, Object>> findElockCar(String carNumber, String carType,
			String elockNum, String elockName, Pageable pageable) {
		StringBuilder sql = new StringBuilder();
		List<Object> list = new ArrayList<Object>();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append("select c.car_number,c.car_type,e.id,e.elock_num,e.elock_name,");
		sql.append(
				"ec.id ec_id,ec.position,ec.seq,ec.berth,sdt.value type_value,sdm.value model_value");
		sql.append(" from elock_car ec left join elock e on ec.elock_id=e.id");
		sql.append(" left join car c on c.id=ec.car_id");
		sql.append(" left join system_dict sdt on e.type=sdt.id");
		sql.append(" left join system_dict sdm on e.model= sdm.id");
		sql.append(" where 1=1");
		if (companyInfoId != null) {
			sql.append(" and ec.company_info_id=?");
			list.add(companyInfoId);
		}
		if (!ConvertUtil.isEmpty(carNumber)) {
			sql.append(" and c.car_number like ?");
			list.add("%" + carNumber + "%");
		}
		if (!ConvertUtil.isEmpty(carType)) {
			sql.append(" and c.car_type like ?");
			list.add("%" + carType + "%");
		}
		if (!ConvertUtil.isEmpty(elockNum)) {
			sql.append(" and e.elock_num like ?");
			list.add("%" + elockNum + "%");
		}
		if (!ConvertUtil.isEmpty(elockName)) {
			sql.append(" and e.elock_name like ?");
			list.add("%" + elockName + "%");
		}

		sql.append(" group by c.car_number,ec.berth order by ec.create_date desc");

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}
		Page<Map<String, Object>> getPage = getNativeDao().findPageMap(sql.toString(),
				objs,
				pageable);

		return getPage;
	}

	public Page<Map<String, Object>> findCarMonitorPage(String carNumber,
			String elockNumber, Long saleOrgId, String startTime, String endTime,
			Pageable pageable) {

		StringBuilder sql = new StringBuilder();
		List<Object> list = new ArrayList<Object>();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append(
				"select er.*,e.car_number,e.position,e.berth from elock_realtime_status er");
		sql.append(
				" left join elock_car e on er.elock_id=e.elock_id left join car_sale_org cso on e.car_id=cso.car where 1=1");
		if (companyInfoId != null) {
			sql.append(" and e.company_info_id=?");
			list.add(companyInfoId);
		}
		if (saleOrgId != null) {
			sql.append(" and cso.sale_org=?");
			list.add(saleOrgId);
		}
		if (!ConvertUtil.isEmpty(carNumber)) {
			sql.append(" and e.car_number like ?");
			list.add("%" + carNumber + "%");
		}
		if (!ConvertUtil.isEmpty(elockNumber)) {
			sql.append(" and e.elock_num like ?");
			list.add("%" + elockNumber + "%");
		}
		if (!ConvertUtil.isEmpty(startTime)) {
			sql.append(" and er.create_date >= ?");
			list.add(startTime + " 00:00:00");
		}
		if (!ConvertUtil.isEmpty(endTime)) {
			sql.append(" and er.create_date < ?");
			list.add(endTime + " 24:00:00");
		}

		sql.append(" order by er.modify_date desc");
		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}
		Page<Map<String, Object>> page = getNativeDao().findPageMap(sql.toString(),
				objs,
				pageable);

		return page;
	}

	public Integer countCarMonitor(String carNumber, String elockNumber, Long saleOrgId,
			String startTime, String endTime, Pageable pageable, Integer page,
			Integer size) {

		StringBuilder sql = new StringBuilder();
		List<Object> list = new ArrayList<Object>();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append("select count(er.id) from elock_realtime_status er");
		sql.append(
				" left join elock_car e on er.elock_id=e.elock_id left join car_sale_org cso on e.car_id=cso.car where 1=1");
		if (companyInfoId != null) {
			sql.append(" and e.company_info_id=?");
			list.add(companyInfoId);
		}
		if (saleOrgId != null) {
			sql.append(" and cso.sale_org=?");
			list.add(saleOrgId);
		}
		if (!ConvertUtil.isEmpty(carNumber)) {
			sql.append(" and e.car_number like ?");
			list.add("%" + carNumber + "%");
		}
		if (!ConvertUtil.isEmpty(elockNumber)) {
			sql.append(" and e.elock_num like ?");
			list.add("%" + elockNumber + "%");
		}
		if (!ConvertUtil.isEmpty(startTime)) {
			sql.append(" and er.create_date >= ?");
			list.add(startTime + " 00:00:00");
		}
		if (!ConvertUtil.isEmpty(endTime)) {
			sql.append(" and er.create_date < ?");
			list.add(endTime + " 24:00:00");
		}

		sql.append(" order by er.modify_date desc");
		if (page != null && size != null) {
			sql.append(" limit " + (size * (page - 1)) + "," + size);
		}
		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}
		Integer count = getNativeDao().findInt(sql.toString(), objs);

		return count;
	}

	public List<Map<String, Object>> findCarMonitorList(String carNumber,
			String elockNumber, Long saleOrgId, String startTime, String endTime,
			Pageable pageable, Integer page, Integer size) {

		StringBuilder sql = new StringBuilder();
		List<Object> list = new ArrayList<Object>();
		Long companyInfoId = WebUtils.getCurrentCompanyInfoId();
		sql.append(
				"select er.*,e.car_number,e.position,e.berth from elock_realtime_status er");
		sql.append(
				" left join elock_car e on er.elock_id=e.elock_id left join car_sale_org cso on e.car_id=cso.car where 1=1");
		if (companyInfoId != null) {
			sql.append(" and e.company_info_id=?");
			list.add(companyInfoId);
		}
		if (saleOrgId != null) {
			sql.append(" and cso.sale_org=?");
			list.add(saleOrgId);
		}
		if (!ConvertUtil.isEmpty(carNumber)) {
			sql.append(" and e.car_number like ?");
			list.add("%" + carNumber + "%");
		}
		if (!ConvertUtil.isEmpty(elockNumber)) {
			sql.append(" and e.elock_num like ?");
			list.add("%" + elockNumber + "%");
		}
		if (!ConvertUtil.isEmpty(startTime)) {
			sql.append(" and er.create_date >= ?");
			list.add(startTime + " 00:00:00");
		}
		if (!ConvertUtil.isEmpty(endTime)) {
			sql.append(" and er.create_date < ?");
			list.add(endTime + " 24:00:00");
		}

		sql.append(" order by er.modify_date desc");
		if (page != null && size != null) {
			sql.append(" limit " + (size * (page - 1)) + "," + size);
		}

		Object[] objs = new Object[list.size()];
		for (int i = 0; i < list.size(); i++) {
			objs[i] = list.get(i);
		}

		List<Map<String, Object>> maps = getNativeDao().findListMap(sql.toString(),
				objs,
				0);

		return maps;
	}
}